package org.example.service;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class DatabaseService {

    private final JdbcTemplate jdbcTemplate;
    private final ObjectMapper objectMapper;

    public DatabaseService(JdbcTemplate jdbcTemplate,ObjectMapper objectMapper) {
        this.jdbcTemplate = jdbcTemplate;
        this.objectMapper = objectMapper;
    }

    /**
     * 执行查询SQL，返回结果列表
     */
    public List<Map<String, Object>> executeQuery(String sql) {
        try {
            log.info("执行SQL查询: {}", sql);
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            log.info("查询成功，返回 {} 行数据", result.size());
            return result;
        } catch (Exception e) {
            log.error("SQL执行失败: {}", sql, e);
            throw new RuntimeException("SQL执行失败: " + e.getMessage());
        }
    }

    /**
     * 获取简化的数据库元数据信息
     */
    public String getSimplifiedDatabaseMetadata() throws JsonProcessingException {
        Map<String, Object> metadata = new HashMap<>();

        try {
            // 获取所有表
            List<String> tables = getTables();
            metadata.put("tables", tables);

            // 获取每张表的字段信息（简化版）
            Map<String, Object> tableColumns = new HashMap<>();
            for (String table : tables) {
                List<Map<String, Object>> schema = getTableSchema(table);

                // 只提取字段名和类型
                List<String> columns = new ArrayList<>();
                for (Map<String, Object> column : schema) {
                    String fieldName = column.get("Field").toString();
                    String fieldType = column.get("Type").toString();
                    columns.add(fieldName + " (" + fieldType + ")");
                }

                tableColumns.put(table, columns);
            }

            metadata.put("table_columns", tableColumns);
            log.info("数据库元数据，包含 {} 张表", tables.size());

        } catch (Exception e) {
            log.error("获取数据库元数据失败", e);
        }
        return objectMapper.writeValueAsString(metadata);
    }



    /**
     * 获取指定数据库中的所有表
     */
    public List<String> getTables() {
        try {
            String sql = "SHOW TABLES ";
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            List<String> tables = new ArrayList<>();
            for (Map<String, Object> row : result) {
                tables.add(row.values().iterator().next().toString());
            }
            log.info("数据库中有 {} 张表", tables.size());
            return tables;
        } catch (Exception e) {
            log.error("获取表列表失败", e);
            return List.of();
        }
    }

    /**
     * 获取表的详细结构信息
     */
    public List<Map<String, Object>> getTableSchema(String table) {
        try {
            String sql = "DESCRIBE " + table;
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            log.info("获取表 {} 的结构，共 {} 个字段", table, result.size());
            return result;
        } catch (Exception e) {
            log.error("获取表结构失败: {}", table, e);
            return List.of();
        }
    }
}